package com.crm.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.mysql.cj.jdbc.Driver;

public class JdbcUtil {
	private static Properties pro;
	static {
		try {
			pro = new Properties();
			InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
			pro.load(is);
			Class.forName(pro.getProperty("jdbc.driver"));
		} catch (ClassNotFoundException e) {
			System.out.println("Connection.Class:"+e);
		} catch (IOException e) {
			System.out.println("Connection.InputStream:"+e);
		}
	}
	
	/**
	 * 数据库连接
	 * */
	public static Connection getConnection() throws Exception{
		return DriverManager.getConnection(pro.getProperty("jdbc.url"), pro.getProperty("jdbc.username"), pro.getProperty("jdbc.pwd"));
	}
	
	/**
	 * 释放资源
	 * */
	public static void closeAll(Connection con,Statement ps,ResultSet rs) {
		try {
			if(rs!=null)rs.close();
			if(ps!=null)ps.close();
			if(con!=null)con.close();
		} catch (SQLException e) {
			System.out.println("JdbcUtil.closeAll:"+e);
		}
	}
	
	/**
	 * 执行DML语句
	 * */
	public static int executeUpdate(String sql, Object...obj) {
		int flag = 0;
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			if(obj!=null) {
				for(int i=0;i<obj.length;i++) {
					ps.setObject(i+1, obj[i]);
				}
			}
			flag = ps.executeUpdate();
		}catch(Exception e) {
			System.out.println("JdbcUtil.DML:"+e);
		}finally {
			closeAll(con, ps, null);
		}
		return flag;
	}
	
	/**
	 * 执行DQL语句
	 * */
	public static List<Map> executeQuery(String sql, Object...obj){
		List<Map> list = new ArrayList<>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			if(obj!=null) {
				for(int i=0;i<obj.length;i++) {
					ps.setObject(i+1, obj[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int n = rsmd.getColumnCount();
			while(rs.next()) {
				Map map = new HashMap();
				for(int i=0;i<n;i++) {
					String columnLabel = rsmd.getColumnLabel(i+1);
					map.put(columnLabel, rs.getObject(columnLabel));
				}
				list.add(map);
			}
		}catch(Exception e){
			System.out.println("JdbcUtil.DQL:"+e);
		}finally {
			closeAll(con, ps, rs);
		}
		return list;
	}
}
